package com.esdk.sql;

import com.esdk.esdk;
import com.esdk.log.DefaultLogLevel;
import com.esdk.log.Logger;
import com.esdk.sql.orm.ConnectionUtils;
import com.esdk.utils.*;
import lombok.NonNull;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

public class SQLAssistant{
	private static SendLog sendlog=new SendLog();
	public static int DefaultSelectLimit=esdk.prop.getInteger("DefaultSelectLimit",50);
	public static boolean IsShowSql=esdk.prop.getBoolean("IsShowSql",true);
	public static String[] ShowSqlExcludes=esdk.str.split(esdk.prop.get("ShowSqlExcludes"));
	public static boolean IsLogSql=esdk.prop.getBoolean("IsLogSql",true);
	public static int LogSqlLevel=DefaultLogLevel.getLevel(esdk.prop.getString("LogSqlLevel","INFO"));
	public static boolean IsShowSpend=esdk.prop.getBoolean("IsShowSpend",true);
	public static int LongQueryTime=esdk.prop.getInteger("LongQueryTime",2000);
	public static String LogSlowQueries=esdk.prop.get("LogSlowQueries","/logs/slow_sql.log");
	public static boolean IsShowConnectionStatus=esdk.prop.getBoolean("IsShowConnectionStatus",false);
	private static boolean IsPrintRequestId=esdk.prop.getBoolean("IsPrintRequestId",false);
	public static Logger _log,_slowlog;

	public static final MysqlDialect mysqlDialect=new MysqlDialect();
	public static final DorisDialect dorisDialect=new DorisDialect();
	public static final PostgresDialect pgsqlDialect=new PostgresDialect();
	public static final OracleDialect oracleDialect=new OracleDialect();
	public static final SqlServerDialect sqlServerDialect=new SqlServerDialect();
	public static SqlDialect defaultDialect=new MysqlDialect();
	public static final String[] SQLKeyWords=new String[] {"as","date","select","from","where","count","sum","min","max","group","top","update","insert","rank","type","sort"};

	public static SqlDialect setDefaultDialect(Connection conn){
		defaultDialect.setDatabaseProductName(conn);
		return getDialect(conn);
	}
	private static Logger getLogger() {
		if(_log==null){
			String logFilePath=esdk.prop.get("LogFilePath","/logs/sql.log");
			String logName=esdk.prop.getString("LogName","sql");
			_log=new Logger(new File(esdk.tool.getPropertiesValueWithSystemEnvirnment(logFilePath)),logName);
		}
		return _log;
	}

	private static Logger getSlowLogger() {
		if(_slowlog==null){
			_slowlog=new Logger(new File(esdk.tool.getPropertiesValueWithSystemEnvirnment(LogSlowQueries)),"slowsql");
			_slowlog.setSilence(true);
		}
		return _slowlog;
	}

	public static void showSql(IShowSql sql) {
		if(!checkExcludesSql(sql)) {
			String s = (SQLAssistant.IsShowSpend?"耗时:" + sql.getExecuteConsuming() + "ms：":"")+sql.getSQL();
			showSql(s, sql.getExecuteConsuming() > LongQueryTime ? DefaultLogLevel._WARN : DefaultLogLevel._INFO);
		}
	}

	public static void showSql(ISelect sql,TimeMeter loadTime,int loadSize) {
		if(!checkExcludesSql(sql)) {
			String s = (SQLAssistant.IsShowSpend?"查询:" + sql.getExecuteConsuming() + "ms,网络:"+loadTime.getElapse()+"ms,数量:"+loadSize+"：":"");
			showSql(s + sql.getSQL(), sql.getExecuteConsuming() > LongQueryTime ? DefaultLogLevel._WARN : DefaultLogLevel._INFO);
		}
	}

	public static void showSql(String sql) {
		showSql(sql,DefaultLogLevel._INFO);
	}

	public static void showSql(BatchInsert bi) {
		if(!checkExcludesSql(bi)) {
			String s = (SQLAssistant.IsShowSpend?"耗时:" + bi.getExecuteConsuming() + "ms,":"")
				+"共插入"+bi.size()+"条记录："+(bi.size()>10?bi.getEllipseSql():bi.getSQL());
			showSql(s , bi.getExecuteConsuming() > LongQueryTime ? DefaultLogLevel._WARN : DefaultLogLevel._INFO);
		}
	}

	public static void showSql(BatchReplace br) {
		if(!checkExcludesSql(br)) {
			String s = (SQLAssistant.IsShowSpend?"耗时:" + br.getExecuteConsuming() + "ms,":"")
				+"共替换"+br.size()+"条记录："+(br.size()>10?br.getEllipseSql():br.getSQL());
			showSql(s, br.getExecuteConsuming() > LongQueryTime ? DefaultLogLevel._WARN : DefaultLogLevel._INFO);
		}
	}

	public static void showSql(BatchMultiUpdate bu) {
		if(!checkExcludesSql(bu)) {
			String s = (SQLAssistant.IsShowSpend?"耗时:" + bu.getExecuteConsuming() + "ms,":"")
				+"共更新"+bu.size()+"条记录："+(bu.size()>10?bu.getEllipseSql():bu.getSQL());
			showSql(s, bu.getExecuteConsuming() > LongQueryTime ? DefaultLogLevel._WARN : DefaultLogLevel._INFO);
		}
	}

	public static String getPettySql(String sql) { //TODO
		return sql; 
	}
	
	public static String getOneLineSql(String sql) {
		return sql.replaceAll("\\r?\\n"," ");
	}
	
	private static void showSql(String sql,int level) {
		if(!sql.endsWith(";"))
			sql+=";";
		if(IsPrintRequestId){
			long requestId=ConnectionUtils.getRequestId();
			if(esdk.obj.isValid(requestId))
					sql="请求ID:"+requestId+","+sql;
		}
		if(IsLogSql) {
			switch(level){
			case DefaultLogLevel._DEBUG:
				if(LogSqlLevel<=level)
					getLogger().debug(sql);
				break;
			case DefaultLogLevel._INFO:
				if(LogSqlLevel<=level)
					getLogger().info(sql);
//				sendlog.info(sql);
				break;
			case DefaultLogLevel._WARN:
				if(LogSqlLevel<=level){
					getLogger().warn(sql);
					if(LogSlowQueries!=null){
						getSlowLogger().warn(sql);
//					sendlog.warn(sql);
					}
				}
				break;
			case DefaultLogLevel._ERROR:
				if(LogSqlLevel<=level){
					getLogger().error(sql);
					sendlog.error(sql);
				}
				break;
			default:
				getLogger().info(sql);
//				sendlog.info(sql);
			}
		}
		else if(IsShowSql && LogSqlLevel<=level) {
			if(level>DefaultLogLevel._INFO)
					esdk.serr(sql);
			else
				esdk.sout(sql);
		}
	}

	private static boolean checkExcludesSql(IShowSql sql) {
		String s= sql.getSQL().replaceAll("\\r?\\n"," ");
		return EasyRegex.findOr(s,ShowSqlExcludes);
	}
	
	static public Object getPrepredSatementValue(Object value) {
		if(value==null)
			return null;
		else if(value instanceof Date date)
			return new Timestamp(date.getTime());
		else return value;
	}

	static public String getStmtSqlValue(Object value){
		return defaultDialect.getStmtSqlValue(value);
	}

	/**解决in参数不能大于2000个的限制，使用or条件串联*/
	public static String getInOr(String columnName,List values,int limit){
		StringBuilder result=new StringBuilder();
		if(values.isEmpty())
			result.append("NULL");
		else{
			for(int i=0, n=values.size();i<n;i+=limit){
				List subList=values.subList(i,esdk.math.min((i+limit),values.size()));
				result.append(columnName).append(" IN(").append(defaultDialect.getCollectionValue(subList)).append(")");
				if(i+limit<n)
					result.append(" OR ");
			}
		}
		return result.toString();
	}

  public static String getAbbreviation(String tableName) {
		if (tableName != null && tableName.length() > 0) {
			String beanName = EasyStr.toCamelCase(tableName, true);
			StringBuffer sb=new StringBuffer();
			for (int i = 0, n = beanName.length(); i < n; i++) {
				if(esdk.str.isCapital(beanName.charAt(i)))
					sb.append(beanName.charAt(i));
			}
			String result=sb.toString().toLowerCase();
			if(esdk.array.contains(SQLKeyWords,result)) {
				result+="_";
			}
			return result;
		} else
			return tableName;
	}

	/**如果有关键字，则用`号包起来*/
	public static String getColumnName(String columnName) {
		if(esdk.array.contains(SQLKeyWords,columnName))
			return "`"+columnName+"`";
		return columnName;
	}

	static public String showSQL(String sql,Object[] params) {
		String result=sql;
		for(int i=0;result.indexOf('?')>=0;i++) {
			//result=result.replaceFirst("\\?",EasyStr.ReplaceAll(SQLAssistant.getStmtSqlValue(params[i]),"$","\\$",false));
			result=result.replaceFirst("\\?",(params[i] instanceof Map)?"null":defaultDialect.getStmtSqlValue(params[i]));
		}
		return result;
	}
	
	public static boolean isPostgreSQL(){
		return defaultDialect.isPostgreSQL();
	}
	public static boolean isDoris(){
		return defaultDialect.isDoris();
	}
	public static boolean isMySQL(){
		return defaultDialect.isMySQL();
	}
	public static boolean isOracle(){
		return defaultDialect.isOracle();
	}
	public static boolean isMsSQL(){
		return defaultDialect.isMsSQL();
	}

	public static ConnMetaData getConnMetaInfo(@NonNull Connection conn){
		ConnMetaData result;
		try{
			DatabaseMetaData dbmd = conn.getMetaData();
			result=new ConnMetaData(dbmd.getDatabaseProductName(),dbmd.getDatabaseProductVersion(),dbmd.getDatabaseMajorVersion());
			return result;

		}catch(SQLException e){
			throw esdk.tool.wrapThrowble(e);
		}
	}

  public static SqlDialect getDialect(Connection conn) {
		if(conn==null)
			return defaultDialect;
		ConnMetaData cmi=getConnMetaInfo(conn);
		if(cmi.isMsSQL())
			return sqlServerDialect;
		else if(cmi.isDoris())
			return dorisDialect;
		else if(cmi.isMySQL())
			return mysqlDialect;
		else if(cmi.isPostgreSQL())
			return pgsqlDialect;
  	else if(cmi.isOracle())
  		return oracleDialect;
  	else
  		return defaultDialect;
  }
	
  public static SqlDialect getDialect() {
  	if(isMsSQL())
  		return sqlServerDialect;
		else if(isDoris())
			return dorisDialect;
  	else if(isMySQL())
  		return mysqlDialect;
		else if(isPostgreSQL())
			return pgsqlDialect;
  	else if(isOracle())
  		return oracleDialect;
  	else
  		return defaultDialect;
  }

	/**sql注入检查，关键字后面逐步添加*/
	public static boolean checkInjectSql(String param){
		boolean result=false;
		if(param!=null){
			String[] templates={"SELECT .*? FROM ","(^| )UNION +SELECT "," OR .*? (AND|OR) ","(^| )INSERT INTO","UPDATE .*? SET","(^| )DROP +(TABLE|VIEW)"
				,"(^| )KILL +\\d+","(^| )ALTER +(TABLE|VIEW)","(^| )(EXEC|CREATE|DROP|TRUNCATE|DELETE|ALTER) .*"};
			for(String item:templates){
				result=result || esdk.regex.find(param,item);
				if(result)
					break;
			}
		}
		return result;
	}

	/**把redis的关键字符换成全角符号，并把回车换成空格*/
  public static String getCacheKey(ISQL isql){
		return "SQL:SELECT:"+isql.getSQL().replaceAll("\r?\n"," ").replace('*','×').replace(':','：');
  }

	public static void main(String[] args){
		String sql="select processdef0_.ID_ as ID1_0_, processdef0_.NAME_ as NAME3_0_, processdef0_.DESCRIPTION_ as DESCRIPT4_0_, processdef0_.VERSION_ as VERSION5_0_, processdef0_.ISTERMINATIONIMPLICIT_ as ISTERMIN6_0_, processdef0_.STARTSTATE_ as STARTSTATE7_0_ from JBPM_PROCESSDEFINITION processdef0_ where processdef0_.NAME_=? order by processdef0_.VERSION_ desc limit ?";
		esdk.tool.assertEquals(getStmtSqlValue("$我好$"),"'\\$我好\\$'");
		esdk.tool.assertEquals(getStmtSqlValue("'你好'"),"'''你好'''");
		esdk.tool.assertEquals(getStmtSqlValue("\\你好\\"),"'\\\\你好\\\\'");
		esdk.tool.assertEquals(SQLAssistant.getInOr("id",esdk.array.toList(1,2,3,4,5,6,7,8,9,10),5),"id IN(1,2,3,4,5) OR id IN(6,7,8,9,10)");
		esdk.tool.assertEquals(SQLAssistant.getInOr("id",esdk.array.toList(1,2,3,4,5,6,7,8,9,10),3),"id IN(1,2,3) OR id IN(4,5,6) OR id IN(7,8,9) OR id IN(10)");
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("pay Create Order"),true);
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("Create Order"),true);
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("payCreate Order"),false);
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("云阳镇鱼跃 行政楼     or   西门门卫"),false);
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("ciciview"),false);
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("delete xxx from "),true);
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("true union select *"),true);
		esdk.tool.assertEquals(SQLAssistant.checkInjectSql("13456"),false);
	}
}
